<?php

include_once('ConnectionManager.php');

function countNumDishByCategory($catID) {
    $query = "SELECT COUNT(DISH_ID) FROM DISH WHERE CAT_ID = $catID";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->fetchArray();
}

function countDishByName($keyword) {
    $select = "SELECT DISH_ID ";
    $from = "FROM DISH ";
    $where = "WHERE DISH_NAME LIKE '%" . $keyword . "%'";

    ConnectionManager::getInstance()->setQuery($select . $from . $where);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->getNumRows();
}

function findDishBy($keyword, $start_position, $row_per_page) {
    $select = "SELECT DISH_ID, CAT_ID, DISH_NAME, DISH_PRICE, DISH_DESCRIPTION FROM DISH ";
    $where = "";


    if (isset($keyword)) {
        $where .= "WHERE DISH_NAME LIKE '%$keyword%'";
    }

    $where .= " LIMIT " . $start_position . ", " . $row_per_page;
    ConnectionManager::getInstance()->setQuery($select . $where);
    return ConnectionManager::getInstance()->query();
}

function insertDish($cateId, $dishName, $dishPrice, $dishDescription) {
    $query = "INSERT INTO DISH (CAT_ID, DISH_NAME, DISH_PRICE, DISH_DESCRIPTION) VALUES ($cateId, '$dishName', '$dishPrice', '$dishDescription')";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function selectCurrentDishID() {
    $query = "SELECT MAX(DISH_ID) AS MAX_ID FROM DISH";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->fetchArray();
}

function findDishById($id) {
    $query = "SELECT * FROM DISH WHERE DISH_ID = " . $id;
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->fetchArray();
}

function updateDish($dishId, $cateId, $dishName, $dishPrice, $dishDescription) {
    $query = "UPDATE DISH
                SET CAT_ID = $cateId,
                    DISH_NAME = '$dishName',
                    DISH_PRICE = $dishPrice,
                    DISH_DESCRIPTION = '$dishDescription' WHERE DISH_ID = $dishId";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function deleteDish($id) {
    $query = "DELETE FROM DISH WHERE DISH_ID = $id";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
}

function findDishByCateAndDay($cateId, $dayId, $keyword) {
	$stringArr = preg_split("/ /", htmlspecialchars(str_replace("d","đ",htmlspecialchars($keyword, ENT_QUOTES)), ENT_QUOTES));	
	$seachString = "";
	foreach($stringArr as $each){
		$seachString .= "$each"."%";
	}
    $select = "SELECT DISTINCT DISH_NAME, DISH_PRICE, D.DISH_ID ";
    $from = "FROM DISH D, DISH_IN_DAY DID ";
    $where = "WHERE D.DISH_ID = DID.DISH_ID AND 
					D.CAT_ID = $cateId AND DID.DAY_ID = $dayId AND 
					D.DISH_NAME LIKE '%".$seachString."'";
    ConnectionManager::getInstance()->setQuery($select . $from . $where);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->query();
}

function findDishByCategory($cateId) {
    $sql = "SELECT DISH_NAME, DISH_PRICE, DISH_ID FROM DISH WHERE CAT_ID = $cateId";
    ConnectionManager::getInstance()->setQuery($sql);
    return ConnectionManager::getInstance()->query();
}

function selectAvailableDish($dayId) {
    $sql = "SELECT DISH_NAME, DISH_PRICE, DISH_ID 
			FROM DISH 
			WHERE CAT_ID = (SELECT CAT_ID FROM CATEGORY WHERE CAT_ORDER = 1) and 
			DISH_ID NOT IN (SELECT DISH_ID FROM DISH_IN_DAY WHERE DAY_ID = $dayId) ORDER BY DISH_NAME";
	echo $sql." sdfads";
    ConnectionManager::getInstance()->setQuery($sql);
    return ConnectionManager::getInstance()->query();
}

function selectDishInDay($dayId) {
    $sql = "SELECT DISH_NAME, DISH_PRICE, DISH_ID FROM DISH WHERE DISH_ID IN (SELECT DISH_ID FROM DISH_IN_DAY WHERE DAY_ID = $dayId)  ORDER BY DISH_NAME";
    ConnectionManager::getInstance()->setQuery($sql);
    return ConnectionManager::getInstance()->query();
}

function isAssignedDish($dishId) {
    $sql = "SELECT COUNT(DISH_ID) AS NUM FROM DISH_IN_DAY WHERE DISH_ID = $dishId";
    ConnectionManager::getInstance()->setQuery($sql);
    ConnectionManager::getInstance()->query();
    $row = ConnectionManager::getInstance()->fetchArray();
    return $row['NUM'] > 0;
}

function getAllDish() {
    $sql = "SELECT * FROM DISH ORDER BY DISH_NAME ASC";
    ConnectionManager::getInstance()->setQuery($sql);
    return ConnectionManager::getInstance()->query();
}

?>